﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using Microsoft.Office.Interop.Excel;
using Microsoft.Xrm.Sdk.Messages;
using Microsoft.Xrm.Sdk;
using Microsoft.Xrm.Sdk.Metadata;

namespace CRMCustom.Parser
{
    public class ExcelParser
    {
        static System.Data.DataTable ConvertMultiDimensioanlArrayToDataTable(Array values)
        {
            int numberOfColumns = values.GetLength(1);
            int numberOfRows = values.GetLength(0) - 1;

            System.Data.DataTable table = new System.Data.DataTable();

            for (int i = 1; i < numberOfColumns + 1; i++)
                table.Columns.Add((string)values.GetValue(1, i));

            for (int i = 1; i < numberOfRows + 1; i++)
            {
                object[] cellValues = new object[numberOfColumns];
                bool isNotEmpty = false;
                for (int j = 0; j < numberOfColumns; j++)
                {
                    cellValues[j] = values.GetValue(i + 1, j + 1);
                    if (cellValues[j] != null && !string.IsNullOrEmpty(cellValues[j].ToString()))
                        isNotEmpty = true;
                }
                if (isNotEmpty)
                    table.Rows.Add(cellValues);
            }
            return table;
        }

        public static bool ParseExcelSheet(string filename, out Exception exception, out EntityContainer entityContainer)
        {
            exception = null;
            DataSet parsedExcelSheet = new DataSet();
            entityContainer = new EntityContainer();

            Microsoft.Office.Interop.Excel.Application ExcelObj2 = null;
            ExcelObj2 = new Microsoft.Office.Interop.Excel.Application();
            Workbook theWorkbook = ExcelObj2.Workbooks.Open(filename, 0, true, 5,
                   "", "", true, XlPlatform.xlWindows, "\t", false, false, 0, true);

            try
            {
                Microsoft.Office.Interop.Excel.Sheets sheets = theWorkbook.Worksheets;
                Microsoft.Office.Interop.Excel.Worksheet entitiesWorksheet = (Microsoft.Office.Interop.Excel.Worksheet)sheets.get_Item(1);
                Microsoft.Office.Interop.Excel.Worksheet attributesWorksheet = (Microsoft.Office.Interop.Excel.Worksheet)sheets.get_Item(2);

                Range entitiesRange = entitiesWorksheet.get_Range("EntitiesTable");
                System.Array entitiesValues = (System.Array)entitiesRange.Cells.Value2;
                parsedExcelSheet.Tables.Add(ExcelParser.ConvertMultiDimensioanlArrayToDataTable(entitiesValues));

                Range attributesRange = attributesWorksheet.get_Range("AttributesTable");
                System.Array attributesValues = (System.Array)attributesRange.Cells.Value2;
                parsedExcelSheet.Tables.Add(ExcelParser.ConvertMultiDimensioanlArrayToDataTable(attributesValues));

                //TODO: Check the number of columns

                //TODO: check data values are proper

                //TODO: check number of sheets

                //TODO: check missing entities in attributes sheet

                //TODO: parse excep sheet content into CRM objects
                ParseDataSet(parsedExcelSheet, out entityContainer);

                //TOD: Check for duplicate entities
                //TOD: Check for duplicate attributes in each entity

                return true;
            }
            catch (Exception ex)
            {
                exception = ex;
                return false;
            }
            finally
            {
                //Close application
                theWorkbook.Close();
            }
        }

        #region Data Mapping
        private static Microsoft.Xrm.Sdk.Label GetLabel(object value)
        {
            return new Microsoft.Xrm.Sdk.Label(value.ToString(), 1033);
        }

        private static bool? GetBoolean(object value)
        {
            return (value.ToString().ToLower() == "yes");
        }

        private static BooleanManagedProperty GetBooleanManagedProperty(object value)
        {
            return new BooleanManagedProperty(value.ToString().ToLower() == "yes");
        }

        private static AttributeRequiredLevel GetRequiredLevel(object value)
        {
            switch (value.ToString())
            {
                case "NoConstraint":
                    return AttributeRequiredLevel.None;
                case "BusinessRecommended":
                    return AttributeRequiredLevel.Recommended;
                case "BusinessRequired":
                    return AttributeRequiredLevel.ApplicationRequired;
                default:
                    return AttributeRequiredLevel.None;
            }

        }

        private static int GetInteger(object value)
        {
            return int.Parse(value.ToString());
        }

        private static AttributeTypeCode GetAttributeTypeCode(object value)
        {
            //TODO: handle the rest of Type codes other than strings
            /*
            * SingleLineOfText
            * OptionSet
            * TwoOptions
            * WholeNumber
            * DecimalNumber
            * Currency
            * MultipleLineOfText
            * DateAndTime
            * Lookup
            */
            switch (value.ToString().ToUpper())
            {
                case "SINGLELINEOFTEXT":
                    return AttributeTypeCode.String;
                case "OPTIONSET":
                    return AttributeTypeCode.Picklist;
                case "TWOOPTIONS":
                    return AttributeTypeCode.Boolean;
                case "WHOLENUMBER":
                    return AttributeTypeCode.Integer;
                case "DECIMALNUMBER":
                    return AttributeTypeCode.Decimal;
                case "CURRENCY":
                    return AttributeTypeCode.Money;
                case "MULTIPLELINEOFTEXT":
                    return AttributeTypeCode.Memo;
                case "DATEANDTIME":
                    return AttributeTypeCode.DateTime;
                case "LOOKUP":
                    return AttributeTypeCode.Lookup;
                default:
                    return AttributeTypeCode.String;
            }
        }

        private static StringFormat GetStringFormat(object value)
        {
            /*Text
Email
TextArea
Url
TickerSymbol
*/
            switch (value.ToString().ToUpper())
            {
                case "TEXT":
                    return StringFormat.Text;
                case "EMAIL":
                    return StringFormat.Email;
                case "TEXTAREA":
                    return StringFormat.TextArea;
                case "URL":
                    return StringFormat.Url;
                case "TICKERSYMBOL":
                    return StringFormat.TickerSymbol;
                default:
                    return StringFormat.Text;
            }
        }

        private static ImeMode GetStringIMEMode(object value)
        {
            //TODO: Handle the rest of IMEMode values
            switch (value.ToString().ToUpper())
            {
                default:
                    return ImeMode.Auto;
            }
        }

        private static AttributeRequiredLevelManagedProperty GetAttributeRequiredLevel(object value)
        {
            return new AttributeRequiredLevelManagedProperty(GetRequiredLevel(value));
        }

        private static OptionSetMetadata GetOptionSetValues(object value)
        {
            string[] values = value.ToString().Split(',');
            int i = 0;
            OptionMetadataCollection valuesCollection = new OptionMetadataCollection();
            foreach (var optionValue in values)
            {
                OptionMetadata optionMetadata = new OptionMetadata(GetLabel(optionValue), i);
                valuesCollection.Add(optionMetadata);
                i++;
            }

            OptionSetMetadata optionSetMetadata = new OptionSetMetadata(valuesCollection);

            return optionSetMetadata;
        }

        private static BooleanOptionSetMetadata GetBooleanOptionSetValues(object value)
        {
            string[] values = value.ToString().Split(',');

            BooleanOptionSetMetadata optionSetMetadata = new BooleanOptionSetMetadata(
                new OptionMetadata(GetLabel(values[0]), 1)
                , new OptionMetadata(GetLabel(values[1]), 0));

            return optionSetMetadata;
        }

        private static IntegerFormat GetIntegerFormat(object value)
        {
            switch (value.ToString().ToUpper())
            {
                case "NONE":
                    return IntegerFormat.None;
                case "DURATION":
                    return IntegerFormat.Duration;
                case "TIMEZONE":
                    return IntegerFormat.TimeZone;
                case "LANGUAGE":
                    return IntegerFormat.Language;
                default:
                    return IntegerFormat.None;
            }
        }

        private static DateTimeFormat GetDateTimeFormat(object value)
        {
            switch (value.ToString().ToUpper())
            {
                case "DATEANDTIME":
                    return DateTimeFormat.DateAndTime;
                case "DATEONLY":
                    return DateTimeFormat.DateOnly;
                default:
                    return DateTimeFormat.DateOnly;
            }
        }

        #endregion Data Mapping

        //TODO: replace _CustomizationPrefix with configurable value from UI
        static string _CustomizationPrefix = "new_";

        private static void ParseDataSet(DataSet parsedExcelSheet, out EntityContainer entityContainer)
        {
            entityContainer = new EntityContainer();
            entityContainer.FlatAttributes = new List<ExtendedAttributeMetadata>();
            entityContainer.Entities = new List<ExtendedEntityMetadata>();

            #region Entities
            foreach (DataRow entityRow in parsedExcelSheet.Tables[0].Rows)
            {
                ExtendedEntityMetadata extendedEntity = new ExtendedEntityMetadata();
                EntityMetadata entity = new EntityMetadata();

                entity.SchemaName = string.Format("{0}{1}", _CustomizationPrefix, entityRow["SystemName"].ToString());
                entity.LogicalName = entityRow["SystemName"].ToString();
                entity.DisplayName = GetLabel(entityRow["DisplayName"]);
                entity.DisplayCollectionName = GetLabel(entityRow["PluralName"]);
                entity.OwnershipType = (entityRow["Ownership"].ToString() == "User or Team") ? OwnershipTypes.UserOwned : OwnershipTypes.OrganizationOwned;

                entity.IsActivity = GetBoolean(entityRow["DefineAsActivity"]);

                entity.Description = GetLabel(entityRow["Description"]);

                //TODO: Map DisplayInWorkplace attribute
                //entity.?? = entityRow["DisplayInWorkplace"].ToString();

                //TODO: Map DisplayInSales attribute
                //entity.?? = entityRow["DisplayInSales"].ToString();

                //TODO: Map DisplayInMarketing attribute
                //entity.?? = entityRow["DisplayInMarketing"].ToString();

                //TODO: Map DisplayInService attribute
                //entity.?? = entityRow["DisplayInService"].ToString();

                //TODO: Map DisplayInSettings attribute
                //entity.?? = entityRow["DisplayInSettings"].ToString();

                //TODO: Map DisplayInResourceCenter attribute
                //entity.?? = entityRow["DisplayInResourceCenter"].ToString();

                extendedEntity.HasNotes = GetBoolean(entityRow["Notes"]).Value;

                extendedEntity.HasActivities = GetBoolean(entityRow["Activities"]).Value;

                entity.IsConnectionsEnabled = GetBooleanManagedProperty(entityRow["Connections"]);

                //TODO: Map SendingEmails attribute
                extendedEntity.SendingEmails = GetBoolean(entityRow["SendingEmails"]);

                entity.IsMailMergeEnabled = GetBooleanManagedProperty(entityRow["MailMerge"]);
                entity.IsDocumentManagementEnabled = GetBoolean(entityRow["DocumentManagement"]).Value;
                entity.IsValidForQueue = GetBooleanManagedProperty(entityRow["Queues"]);
                entity.AutoRouteToOwnerQueue = GetBoolean(entityRow["QueuesAutomaticallyMoveRecords"]).Value;
                entity.IsDuplicateDetectionEnabled = GetBooleanManagedProperty(entityRow["DuplicateDetection"]);
                entity.IsAuditEnabled = GetBooleanManagedProperty(entityRow["Auditing"]);
                entity.IsVisibleInMobile = GetBooleanManagedProperty(entityRow["MobileExpress"]);
                entity.IsReadingPaneEnabled = GetBoolean(entityRow["ReadingPaneInOutlook"]).Value;
                entity.IsAvailableOffline = GetBoolean(entityRow["OffilineCapabilityInOutlook"]).Value;

                extendedEntity.PrimaryAttribute = new StringAttributeMetadata
                {
                    SchemaName = (entity.IsActivity.Value) ? "Subject" : _CustomizationPrefix + entityRow["PrimaryFieldName"].ToString(),
                    RequiredLevel = GetAttributeRequiredLevel(entityRow["PrimaryFieldRequirementLevel"]),
                    MaxLength = int.Parse(entityRow["PrimaryFieldMaximumLength"].ToString()),
                    Format = StringFormat.Text,
                    DisplayName = GetLabel(entityRow["PrimaryFieldDisplayName"]),
                    Description = GetLabel(entityRow["PrimaryFieldDesciption"])
                };

                extendedEntity.Metadata = entity;

                extendedEntity.IncludeInTransaction = true;

                entityContainer.Entities.Add(extendedEntity);
            }
            #endregion Entities

            #region FlatAttributes
            foreach (DataRow attributeRow in parsedExcelSheet.Tables[1].Rows)
            {
                AttributeMetadata attribute = new AttributeMetadata();




                #region Different types of attribute metadata
                switch (GetAttributeTypeCode(attributeRow["Type"]))
                {
                    #region Not Handled
                    default:
                        break;
                    case AttributeTypeCode.CalendarRules:
                        break;
                    case AttributeTypeCode.Customer:
                        break;
                    case AttributeTypeCode.BigInt:
                        break;
                    case AttributeTypeCode.Double:
                        break;
                    case AttributeTypeCode.EntityName:
                        break;
                    case AttributeTypeCode.ManagedProperty:
                        break;
                    case AttributeTypeCode.Owner:
                        break;
                    case AttributeTypeCode.PartyList:
                        break;
                    case AttributeTypeCode.State:
                        break;
                    case AttributeTypeCode.Status:
                        break;
                    case AttributeTypeCode.Uniqueidentifier:
                        break;
                    case AttributeTypeCode.Virtual:
                        break;
                    #endregion Not Handled

                    case AttributeTypeCode.Boolean:
                        //Two options
                        BooleanAttributeMetadata booleanAttribute = new BooleanAttributeMetadata();

                        booleanAttribute.DefaultValue = false;

                        booleanAttribute.OptionSet = GetBooleanOptionSetValues(attributeRow["TwoOptionsValues"]);

                        attribute = booleanAttribute;
                        break;
                    case AttributeTypeCode.DateTime:
                        //DateAndTime
                        DateTimeAttributeMetadata datetimeAttribute = new DateTimeAttributeMetadata();
                        datetimeAttribute.Format = GetDateTimeFormat(attributeRow["DateAndTimeFormat"]);

                        attribute = datetimeAttribute;
                        break;
                    case AttributeTypeCode.Decimal:
                        //DecimalNumber
                        DecimalAttributeMetadata decimalAttribute = new DecimalAttributeMetadata();

                        decimalAttribute.MinValue = (decimal)GetInteger(attributeRow["DecimalNumberMinimumValue"]);
                        decimalAttribute.MaxValue = (decimal)GetInteger(attributeRow["DecimalNumberMaximumValue"]);
                        decimalAttribute.Precision = GetInteger(attributeRow["DecimalNumberPrecision"]);

                        attribute = decimalAttribute;
                        break;

                    case AttributeTypeCode.Integer:
                        //Whole Number
                        IntegerAttributeMetadata integerAttribute = new IntegerAttributeMetadata();
                        integerAttribute.Format = GetIntegerFormat(attributeRow["WholeNumberFormat"]);
                        integerAttribute.MinValue = GetInteger(attributeRow["WholeNumberMinumum"]);
                        integerAttribute.MaxValue = GetInteger(attributeRow["WholeNumberMaximum"]);

                        attribute = integerAttribute;
                        break;
                    case AttributeTypeCode.Lookup:
                        LookupAttributeMetadata lookupAttribute = new LookupAttributeMetadata();
                        lookupAttribute.Targets = new string[2] { _CustomizationPrefix + attributeRow["OwnerEntity"].ToString().ToLower(), attributeRow["LookupTargetRecordType"].ToString() };

                        attribute = lookupAttribute;
                        break;

                    case AttributeTypeCode.Memo:
                        //MultipleLineOfText
                        MemoAttributeMetadata memoAttribute = new MemoAttributeMetadata();
                        memoAttribute.MaxLength = GetInteger(attributeRow["MultipleLinesOfTextMaximum"]);

                        attribute = memoAttribute;
                        break;
                    case AttributeTypeCode.Money:
                        //Currency
                        MoneyAttributeMetadata moneyAttribute = new MoneyAttributeMetadata();

                        moneyAttribute.MinValue = (double)GetInteger(attributeRow["CurrencyMinimum"]);
                        moneyAttribute.MaxValue = (double)GetInteger(attributeRow["CurrencyMaximum"]);
                        moneyAttribute.Precision = GetInteger(attributeRow["CurrencyPrecision"]);

                        attribute = moneyAttribute;
                        break;

                    case AttributeTypeCode.Picklist:
                        //Option Set
                        PicklistAttributeMetadata piclistAttribute = new PicklistAttributeMetadata();
                        piclistAttribute.DefaultFormValue = 0;

                        //Use existing option set
                        if (GetBoolean(attributeRow["OptionSetUseExistingOptionSet"]).Value)
                        {
                            piclistAttribute.OptionSet = new OptionSetMetadata();
                            piclistAttribute.OptionSet.Name = attributeRow["OptionSetExistingOptionSetName"].ToString();
                        }
                        else
                        {
                            piclistAttribute.OptionSet = GetOptionSetValues(attributeRow["OptionSetValues"]);
                            piclistAttribute.OptionSet.Name = (_CustomizationPrefix + attributeRow["SystemName"] + "Options").ToLower();
                            piclistAttribute.OptionSet.DisplayName = GetLabel(attributeRow["DisplayName"]);

                            piclistAttribute.OptionSet.IsGlobal = true;
                            piclistAttribute.OptionSet.OptionSetType = OptionSetType.Picklist;
                        }

                        attribute = piclistAttribute;
                        break;

                    case AttributeTypeCode.String:
                        StringAttributeMetadata stringAttribute = new StringAttributeMetadata();

                        stringAttribute.Format = GetStringFormat(attributeRow["SingleLineOfTextFormat"]);
                        stringAttribute.MaxLength =
                            string.IsNullOrEmpty(attributeRow["SingleLineOfTextMaximumLength"].ToString())
                            ? 100 : int.Parse(attributeRow["SingleLineOfTextMaximumLength"].ToString());

                        stringAttribute.ImeMode = GetStringIMEMode(attributeRow["SingleLineOfTextIMEMode"]);

                        attribute = stringAttribute;
                        break;
                }
                #endregion Different types of attribute metadata

                attribute.SchemaName = string.Format("{0}{1}", _CustomizationPrefix, attributeRow["SystemName"].ToString());
                attribute.DisplayName = GetLabel(attributeRow["DisplayName"]);
                attribute.LogicalName = attributeRow["SystemName"].ToString();

                attribute.RequiredLevel = new AttributeRequiredLevelManagedProperty(GetRequiredLevel(attributeRow["RequirementLevel"]));

                //TODO: Map Searchable attribute
                //attribute.?? = attributeRow["Searchable"].ToString();

                //TODO: Map FieldSecurity attribute
                //attribute.?? = attributeRow["FieldSecurity"].ToString();

                attribute.IsAuditEnabled = GetBooleanManagedProperty(attributeRow["Auditing"]);
                attribute.Description = GetLabel(attributeRow["Description"]);

                ExtendedAttributeMetadata attributemetadata = new ExtendedAttributeMetadata(attribute);
                
                attributemetadata.OwnerEntity = _CustomizationPrefix + attributeRow["OwnerEntity"].ToString();

                entityContainer.FlatAttributes.Add(attributemetadata);
            }
            #endregion Attributes

            MapFlatAttributesToEntities(entityContainer);
        }

        public static void MapFlatAttributesToEntities(EntityContainer entityContainer)
        {
            #region Mapping flat attributes to their parent entities
            foreach (var entity in entityContainer.Entities)
            {
                entity.Attributes = new List<ExtendedAttributeMetadata>();
                entity.Attributes = entityContainer.FlatAttributes.Where(att => att.OwnerEntity == entity.Metadata.SchemaName).ToList();
            }
            #endregion Mapping flat attributes to their parent entities
        }


    }
}
